package com.ruifu.pls.repository;

import com.ruifu.pls.entity.InvLotLocId;
import com.ruifu.pls.entity.InvLotLocIdId;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.List;

/**
 * ********************************************************
 * * @desc  ：
 * * @author  ${user}
 * * @date    ${date}
 * * @version v1.0
 * * ${tags}
 * *******************************************************
 */
public interface GInventoryRepository
        extends
        PagingAndSortingRepository<InvLotLocId,InvLotLocIdId>, JpaSpecificationExecutor<InvLotLocId>
{

    @Query(value="select    max(a. LOTNUM),max(a.LOCATIONID) locationid,max(a. TRACEID) traceid,max(a. CUSTOMERID)  customerid,max(a. SKU) sku,sum(a. QTY) qty,max(a. QTYALLOCATED),max(a. QTYRPIN),max(a. QTYRPOUT),max(a. QTYMVIN),max(a. QTYMVOUT),max(a. QTYONHOLD) qtyonhold,max(a. ADDTIME),max(a. ADDWHO),max(a. EDITTIME) edittime,max(a. EDITWHO),max(a. NETWEIGHT),max(a. GROSSWEIGHT),max(a. CUBIC),max(a. PRICE),max(a. ONHOLDLOCKER),max(a.LPN),max(a. QTYPA),max(a.LASTMAINTENANCEDATE),max(b.WAREHOUSEID) WAREHOUSEID,max(b. ZONEGROUP),max(b. ZONE),max(b. LOCATIONID), max(c.CUSTOMERID),max(c. SKU),max(c. HAZARD_FLAG),max(c. DESCR_E),max(c.DESCR_C),max(c. PACKID),max(c. TARE),max(c. ACTIVE_FLAG),max(c. LOTID),max(c. CARTONGROUP),max(c. PUTAWAYZONE),max(c. CUBE),max(c. PUTAWAYLOCATION),max(c. GROSSWEIGHT),max(c. NETWEIGHT),max(c. CYCLECLASS),max(c. LASTCYCLECOUNT),max(c. REORDERQTY),max(c. PRICE),max(c. SHELFLIFEFLAG),max(c. INBOUNDLIFEDAYS),max(c. SOFTALLOCATIONRULE),max(c. ALLOCATIONRULE),max(c. SHELFLIFETYPE),max(c. OUTBOUNDLIFEDAYS),max(c. ADDTIME),max(c. ADDWHO),max(c. EDITTIME),max(c. EDITWHO), max(c.ALTERNATE_SKU4),max(c. ALTERNATE_SKU5) ALTERNATE_SKU5,max(c. NOTES),max(c. PUTAWAYRULE),max(c. ALTERNATE_SKU1),max(c. ALTERNATE_SKU2),max(c. ALTERNATE_SKU3),max(c. RESERVEDFIELD05),max(c. SKU_GROUP1),max(c. SKU_GROUP2),max(c. SKU_GROUP3),max(c. SKU_GROUP4),max(c. SKU_GROUP5),max(c. RESERVEDFIELD01),max(c. RESERVEDFIELD02),max(c. RESERVEDFIELD03),max(c. RESERVEDFIELD04),max(c. DEFAULTRECEIVINGUOM),max(c. DEFAULTSHIPMENTUOM),max(c. DEFAULTHOLD),max(c. ROTATIONID),max(c. COPYPACKIDTOLOTATT12),max(c. QTYMIN),max(c. QTYMAX),max(c. SHELFLIFE),max(c. HSCODE),max(c. RESERVEDFIELD06),max(c. RESERVEDFIELD07),max(c. RESERVEDFIELD08),max(c. RESERVEDFIELD09),max(c. RESERVEDFIELD10),max(c. RESERVEDFIELD11),max(c. RESERVEDFIELD12),max(c. OVERRCVPERCENTAGE),max(c. REPLENISHRULE),max(c. SPECIALMAINTENANCE),max(c. FIRSTOP),max(c. MEDICALTYPE),max(c. APPROVALNO),max(c. SN_ASN_QTY),max(c. SN_SO_QTY),max(c. INVCHGWITHSHIPMENT),max(c. FREIGHTCLASS),max(c. TARIFFID),max(c. KITFLAG),max(c. RESERVECODE),max(c. REPORTUOM),max(c. SKULENGTH),max(c. SKUWIDTH),max(c. SKUHIGH),max(c. QCTIME),max(c. QCRULE),max(c. FIRSTINBOUNDDATE),max(c. RESERVEDFIELD13),max(c. RESERVEDFIELD14),max(c. RESERVEDFIELD15),max(c. SKU_GROUP6),max(c. SKU_GROUP7),max(c. SKU_GROUP8),max(c. SKU_GROUP9),max(c. MEDICINESPECICALCONTROL),max(c. SERIALNOCATCH),max(c. SCANWHENCASEPICKING),max(c. SCANWHENPIECEPICKING),max(c. SCANWHENCHECK),max(c. SCANWHENRECEIVE),max(c. SCANWHENPUTAWAY),max(c. SHELFLIFEALERTDAYS),max(c. RESERVEDFIELD17),max(c. RESERVEDFIELD18),max(c. RESERVEDFIELD16),max(c. CHK_SCN_UOM),max(c. ALTERNATIVE_PUTAWAYZONE1),max(c. ALTERNATIVE_PUTAWAYZONE2),max(c. SCANWHENPACK),max(c. SCANWHENMOVE),max(c. IMAGEADDRESS),max(c. ONESTEPALLOCATION),max(c. ORDERBYSQL),max(c. SECONDSERIALNOCATCH),max(c. ALLOWRECEIVING),max(c. ALLOWSHIPMENT),max(c. INBOUNDSERIALNOQTYCONTROL),max(c. OUTBOUNDSERIALNOQTYCONTROL),max(c. DEFAULTCARTONTYPE),max(c. BREAKCS),max(c. BREAKEA),max(c. BREAKIP),max(c. PRINTMEDICINEQCREPORT),max(c. SCANWHENQC),max(c. OVERRECEIVING),max(c. DEFAULTSUPPLIERID),max(c. CAS_ULD_PHR),max(c. SCANWHENINVSCAN),max(c. MAINTENANCEREASON),max(c. PICKPACKID), max(d.LOTNUM) lotnum,max(d. CUSTOMERID),max(d. SKU), max(d.LOTATT01) LOTATT01, max(d.LOTATT02) LOTATT02, max(d.LOTATT03),max( d.LOTATT04) LOTATT04,max( d.LOTATT05) LOTATT05,max( d.LOTATT06), max(d.LOTATT07), max(d.LOTATT08) LOTATT08, max(d.LOTATT09),max( d.LOTATT10),max(d. LOTATT11),max(d. LOTATT12),max(d. ADDTIME),max(d. ADDWHO),max(d. EDITTIME),max(d. EDITWHO),max(d. RECEIVINGTIME), max(d.QCREPORTFILENAME)   from inv_lot_loc_id a left join  VIEW_MultiWarehouse b on a.locationid=b.locationid\n" +
            "left join bas_sku c on a.customerid=c.customerid and a.sku=c.sku \n" +
            "left join inv_lot_att d on a.lotnum=d.lotnum where a.sku in('CB060001-1100', 'CB060001-1110') and (b.warehouseid='NSLDS' or b.warehouseid='JD_TEST')\n" +
            "group by b.warehouseid,a.sku,a.customerid,d.lotatt01,d.lotatt02,d.lotatt04,d.lotatt05,d.lotatt08",nativeQuery=true)
public List<InvLotLocId> load();
}
